<?php

namespace App\Repositories;

use App\Enums\RedisKeyEnum;
use App\Exceptions\BasicException;
use App\Services\Tools\CommonService;
use App\Services\Tools\HelperService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;

//引入hash

class UserRechargeRepository
{
    /**
     * 获取充值总数
     *
     * @param $param
     * @return bool
     */
    public function userRecharge($param)
    {
        $result = app('model_user_recharge')->where('status', 2)->whereBetween('pay_time', [$param['start_time'], $param['end_time']]);
        if (!empty($param['grade']) && empty($param['keyword'])) {
            $user_rank = app('model_user')->where('rank_id', $param['grade'])->pluck('user_id')->toArray();
            $arr_uids = $user_rank;
        }
        if (!empty($param['keyword']) && empty($param['grade'])) {
            $user_data = app('model_user_data')->where('nickname', 'like', "%{$param['keyword']}%")->orWhere('user_code', 'like', "%{$param['keyword']}%")->pluck('user_id')->toArray();
            $user = app('model_user')->where('mobile', 'like', "%{$param['keyword']}%")->pluck('user_id')->toArray();
            $arr_uids = array_values(array_unique(array_merge($user_data, $user)));
        }

        if (!empty($param['keyword']) && !empty($param['grade'])) {
            $user_data_ids = app('model_user_data')->where('nickname', 'like', "%{$param['keyword']}%")->orWhere('user_code', 'like', "%{$param['keyword']}%")->select('user_id')->pluck('user_id')->toArray();
            $user_rank_ids = app('model_user')->where('rank_id', $param['grade'])->pluck('user_id')->toArray();
            $user_data = array_values(array_intersect($user_data_ids, $user_rank_ids));
            $user = app('model_user')->where('mobile', 'like', "%{$param['keyword']}%")->where('rank_id', $param['grade'])->pluck('user_id')->toArray();
            $arr_uids = array_values(array_unique(array_merge($user_data, $user)));
        }

        if (isset($arr_uids)) {
            $result = $result->where(function ($query) use ($arr_uids) {
                $query->whereIn('user_id', $arr_uids);
            });
        }
        $result = $result->select(DB::raw('sum(money) as  new_money'), 'user_id')->first()->toArray();
        $result = $result['new_money'] ?? 0;

        return $result;
    }

    /**
     * 获取充值列表
     *
     * @param $param
     * @return bool
     */

    public function rechargeList($param, $page = 1, $perPage = 100)
    {
        $result = app('model_user_recharge')->where('status', 2)->whereBetween('pay_time', [$param['start_time'], $param['end_time']]);
        if (!empty($param['grade']) && empty($param['keyword'])) {
            $user_rank = app('model_user')->where('rank_id', $param['grade'])->pluck('user_id')->toArray();
            $arr_uids = $user_rank;
        }
        if (!empty($param['keyword']) && empty($param['grade'])) {
            $user_data = app('model_user_data')->where('nickname', 'like', "%{$param['keyword']}%")->orWhere('user_code', 'like', "%{$param['keyword']}%")->pluck('user_id')->toArray();
            $user = app('model_user')->where('mobile', 'like', "%{$param['keyword']}%")->pluck('user_id')->toArray();
            $arr_uids = array_values(array_unique(array_merge($user_data, $user)));
        }

        if (!empty($param['keyword']) && !empty($param['grade'])) {
            $user_data_ids = app('model_user_data')->where('nickname', 'like', "%{$param['keyword']}%")->orWhere('user_code', 'like', "%{$param['keyword']}%")->select('user_id')->pluck('user_id')->toArray();
            $user_rank_ids = app('model_user')->where('rank_id', $param['grade'])->pluck('user_id')->toArray();
            $user_data = array_values(array_intersect($user_data_ids, $user_rank_ids));
            $user = app('model_user')->where('mobile', 'like', "%{$param['keyword']}%")->where('rank_id', $param['grade'])->pluck('user_id')->toArray();
            $arr_uids = array_values(array_unique(array_merge($user_data, $user)));
        }

        if (isset($arr_uids)) {
            $result = $result->where(function ($query) use ($arr_uids) {
                $query->whereIn('user_id', $arr_uids);
            });
        }
        $result = $result->select(DB::raw('sum(money) as money'), DB::raw("from_unixtime(pay_time,'%Y%m%d')"))->groupBy(DB::raw("from_unixtime(pay_time,'%Y%m%d')"))->paginate($perPage, ['*'], '', $page);

        return $result;
    }

    /**
     * 获取用户充值列表
     *
     * @param $where
     * @param $page
     * @param $perPage
     * @param $channel
     * @param $keyword
     * @return bool
     */
    public function userRechargeList($where = [], $page = 1, $perPage = 20, $channel = '', $keyword = '')
    {
        $list = app('model_user_recharge')->with(['user_data:id,user_id,user_code,nickname', 'user:user_id,channel_no']);
        if ($where) {
            $list = $list->where($where);
        };
        if ($channel !== '') {
            $list = $list->where(function ($query) use ($channel) {
                $query->whereHas('user', function ($query) use ($channel) {
                    $query->where('channel_no', $channel);
                });
            });
        };
        if ($keyword !== '') {
            $list = $list->where(function ($query) use ($keyword) {
                $query->where('user_id', 'like', "%{$keyword}%")
                    ->orWhere('order_sn', 'like', "%{$keyword}%")
                    ->orWhere('trade_no', 'like', "%{$keyword}%")
                    ->orWhereHas('user_data', function ($query) use ($keyword) {
                        $query->where('user_code', 'like', "%{$keyword}%");
                    });
            });
        };
        $list = $list->orderBy('add_time', 'desc')
            ->orderBy('pay_time', 'desc')
            ->paginate($perPage, ['*'], '', $page);

        return $list;
    }

    //获取用户充值数据
    public function getList($param)
    {
        $payType = $param['pay_type'] ?? 0;
        $payChannel = $param['pay_channel'] ?? '';
        $payStatus = $param['pay_status'] ?? 0;
        $userCode = $param['user_code'] ?? 0;
        $orderSn = $param['order_sn'] ?? '';
        $tradeNo = $param['trade_no'] ?? '';
        $addTimeStart = $param['add_time_start'] ?? '';
        $addTimeEnd = $param['add_time_end'] ?? '';
        $payTimeStart = $param['pay_time_start'] ?? '';
        $payTimeEnd = $param['pay_time_end'] ?? '';
        $receiveAccountId = $param['receive_account_id'] ?? 0;
//        $receiveAccountName = $param['receive_account_name'] ?? '';
        $pageSize = $param['page_size'] ?? 10;
        $refundStatus = $param['refund_status']??0;
        $receiveAccountName = $param['merchant_name'] ?? '';

        $where = $whereFunc = [];
        if ($payType > 0) {
            $where[] = ['type', '=', $payType];
        }
        if ($refundStatus > 0) {
            $where[] = ['refund_status', '=', $refundStatus];
        }
        if ($payChannel) {
            $userIds = app('model_user')->where('channel_no', $payChannel)->pluck('user_id');
            $whereFunc = function ($query) use ($userIds) {
                $query->whereIn('user_id', $userIds);
            };
        }
        if ($payStatus) {
            //筛选支付状态
            if(in_array($payStatus,[1,2])){
                $where[] = ['status', '=', $payStatus];
                $where[] = ['refund_status', '=', 0];
            }elseif (in_array($payStatus, [3,4,5])) {//筛选退款状态
                switch ($payStatus) {
                    case 3://退款中
                        $refundStatus = 2;
                        break;
                    case 4://退款成功
                        $refundStatus = 1;
                        break;
                    case 5://退款失败
                        $refundStatus = 3;
                        break;
                }
                $where[] = ['refund_status', '=', $refundStatus];
            }
        }
        if ($userCode) {
            $userId = app('model_user_data')->where('user_code', $userCode)->value('user_id');
            $where[] = ['user_id', '=', $userId];
        }
        if ($orderSn) {
            $where[] = ['order_sn', '=', $orderSn];
        }
        if ($tradeNo) {
            $where[] = ['trade_no', '=', $tradeNo];
        }
        if ($addTimeStart) {
            $where[] = ['add_time', '>=', strtotime($addTimeStart . ' 0:0:0')];
        }
        if ($addTimeEnd) {
            $where[] = ['add_time', '<=', strtotime($addTimeEnd . ' 23:59:59')];
        }
        if ($payTimeStart) {
            $where[] = ['pay_time', '>=', strtotime($payTimeStart . ' 0:0:0')];
        }
        if ($payTimeEnd) {
            $where[] = ['pay_time', '<=', strtotime($payTimeEnd . ' 23:59:59')];
        }
        if ($receiveAccountId > 0) {
            $where[] = ['merchant_type', '=', $receiveAccountId];
        }
        if (!empty($receiveAccountName)) {
            $where[] = ['merchant_name', 'like', '%'.$receiveAccountName.'%'];
        }
        
        $list = app('model_user_recharge')
            ->select('id', 'user_id', 'order_sn', 'trade_no', 'money', 'gold', 'status', 'type', 'add_time', 'pay_time', 'merchant_type','merchant_name', 'refund_status')
            ->where($where)
            ->where($whereFunc)
            ->orderBy('add_time', 'desc')
            ->orderBy('pay_time', 'desc')
            ->paginate($pageSize)->toArray();

        //批量查询用户信息
        $userIds = array_column($list['data'], 'user_id');
        $users = app('model_user_data')
            ->select('user_id', 'user_code', 'nickname')
            ->whereIn('user_id', $userIds)->get()->toArray();
        $users = array_column($users, null, 'user_id');
        //查询所有收款商户信息
        $merchants = app('model_pay_merchant')->select('id', 'name')->pluck('name', 'id')->toArray();

        // 获取所有的财务系统商户
        $merchants = app('model_user_recharge')
            ->where('merchant_name', '<>', ' ')
            ->select('merchant_type','merchant_name')
            ->groupBy('merchant_name')
            ->get()->toArray();
        $list['merchants'] = $merchants;

        $this->userRechargeFieldConversion($list['data']);

        foreach ($list['data'] as &$v) {
            if (isset($users[$v['user_id']])) {
                $v['user_code'] = $users[$v['user_id']]['user_code'];
                $v['nickname'] = $users[$v['user_id']]['nickname'];
            } else {
                $v['user_code'] = '';
                $v['nickname'] = '';
            }
//            $v['merchant_name'] = $merchants[$v['merchant_type']] ?? '';
        }

        return $list;
    }

    //查询导出数据（连接查询）
    public function getExportList($param, $offset, $fileSize)
    {
        $payType = $param['pay_type'] ?? 0;
        $payChannel = $param['pay_channel'] ?? '';
        $payStatus = $param['pay_status'] ?? 0;
        $userCode = $param['user_code'] ?? 0;
        $orderSn = $param['order_sn'] ?? '';
        $tradeNo = $param['trade_no'] ?? '';
        $addTimeStart = $param['add_time_start'] ?? '';
        $addTimeEnd = $param['add_time_end'] ?? '';
        $payTimeStart = $param['pay_time_start'] ?? '';
        $payTimeEnd = $param['pay_time_end'] ?? '';
        $receiveAccount = $param['receive_account'] ?? 0;
        $receiveAccountName = $param['receive_account_name'] ?? '';

        $where = $whereFunc = [];
        if ($payType) {
            $where[] = ['uc.type', '=', $payType];
        }
        if ($payChannel) {
            $userIds = app('model_user')->where('channel_no', $payChannel)->pluck('user_id');
            $whereFunc = function ($query) use ($userIds) {
                $query->whereIn('uc.user_id', $userIds);
            };
        }
        if ($payStatus) {
            $where[] = ['uc.status', '=', $payStatus];
        }
        if ($userCode) {
            $userId = app('model_user_data')->where('user_code', $userCode)->value('user_id');
            $where[] = ['uc.user_id', '=', $userId];
        }
        if ($orderSn) {
            $where[] = ['uc.order_sn', '=', $orderSn];
        }
        if ($tradeNo) {
            $where[] = ['uc.trade_no', '=', $tradeNo];
        }
        if ($addTimeStart) {
            $where[] = ['uc.add_time', '>=', strtotime($addTimeStart . ' 0:0:0')];
        }
        if ($addTimeEnd) {
            $where[] = ['uc.add_time', '<=', strtotime($addTimeEnd . ' 23:59:59')];
        }
        if ($payTimeStart) {
            $where[] = ['uc.pay_time', '>=', strtotime($payTimeStart . ' 0:0:0')];
        }
        if ($payTimeEnd) {
            $where[] = ['uc.pay_time', '<=', strtotime($payTimeEnd . ' 23:59:59')];
        }
        if ($receiveAccount) {
            $where[] = ['uc.account_type', '=', $receiveAccount];
        }
        if (!empty($receiveAccountName)) {
            $where[] = ['merchant_name', 'like', '%' . $receiveAccountName . '%'];
        }

        $list = app('model_user_recharge')->from('ln_user_recharge as uc')
            ->leftJoin('ln_user_data as ud', 'uc.user_id', '=', 'ud.user_id')
            ->select('uc.id', 'uc.user_id', 'ud.user_code', 'ud.nickname', 'uc.order_sn', 'uc.trade_no', 'uc.money', 'uc.gold', 'uc.status', 'uc.type', 'uc.add_time', 'uc.pay_time', 'uc.merchant_name')
            ->where($where)
            ->where($whereFunc)
            ->offset($offset)->limit($fileSize)->get();

        foreach ($list as &$v) {
            $v->pay_type = CommonService::payType($v->type);
            $v->add_time = date('Y-m-d H:i:s', $v->add_time);
            if ($v->pay_time > 0) {
                $v->pay_time = date('Y-m-d H:i:s', $v->pay_time);
            } else {
                $v->pay_time = '';
            }
            if ($v->status == 1) {
                $v->pay_status = '未支付';
            } else {
                if ($v->status == 2) {
                    $v->pay_status = '已支付';
                } else {
                    $v->pay_status = '敬请期待';
                }
            }
            unset($v['status']);
        }

        return $list;
    }

    private function userRechargeFieldConversion(&$data)
    {
        foreach ($data as &$v) {
            $v['pay_type'] = CommonService::payType($v['type']);
            unset($v['type']);
            $v['add_time'] = date('Y-m-d H:i:s', $v['add_time']);

            if ($v['pay_time'] > 0) {
                $v['pay_time'] = date('Y-m-d H:i:s', $v['pay_time']);
            } else {
                $v['pay_time'] = '';
            }
            if ($v['status'] == 1) {
                $v['pay_status'] = '未支付';
            } else {
                if ($v['status'] == 2) {
                    $v['pay_status'] = '已支付';
                } else {
                    $v['pay_status'] = '敬请期待';
                }
            }
            if ($v['refund_status'] != 0) {
               switch ($v['refund_status']) {
                   case 1:
                        $v['pay_status'] = '退款成功';
                       break;
                    case 2:
                        $v['pay_status'] = '退款中';
                        break;
                    case 3:
                        $v['pay_status'] = '退款失败';
                        break;
               }
            }
            unset($v['status']);
        }
    }

    /**
     * 用户充值统计
     *
     * @param $where
     * @return bool
     */
    public function userRechargeTotal($where)
    {
        $total = app('model_user_recharge')->where($where)->select(DB::raw('sum(money) as total_money'), 'type')->groupBy('type')->get()->toArray();

        return $total;
    }

    /**
     * 用户总充值统计
     *
     * @param $user_id
     * @return bool
     */

    public function userAllRechargeTotal($user_id)
    {
        $total = app('model_user_recharge')->where(['user_id' => $user_id, 'status' => 2])->select(DB::raw('sum(money) as total_money'))->groupBy('user_id')->first();

        return $total;
    }

    /**
     * 充值新用户
     *
     * @param  int  $startTime
     * @param  int  $endTime
     * @return array
     */
    public function getNewUserRecharge(int $startTime, int $endTime)
    {
        $sql = "
                SELECT ud.user_code,ud.nickname,SUM(ur.money) AS money FROM ln_user_recharge ur
                        LEFT JOIN ln_user_data ud ON ur.user_id = ud.user_id
                        WHERE ur. STATUS = 2 AND ur.user_id IN (SELECT user_id FROM ln_user WHERE register_time BETWEEN {$startTime} AND {$endTime})
                        GROUP BY ur.user_id";

        $res = DB::select($sql);
        foreach ($res as &$value) {
            $value->nickname = HelperService::wxNickNameFormat($value->nickname);
        }

        return $res;
    }
}


?>